"""
SqlAlchermy ORM关系 - 多对多
"""
import os
import sys

path = os.path.dirname(__file__)
sys.path.append(os.path.join(path, '..'))

from datetime import datetime
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey, Table
from sqlalchemy.orm import relationship, backref
# from sqlalchemy.orm.collections import InstrumentedList
from conf.mysql_config import BASE, SESSION

book_tag_table = Table(
    'books_tags',
    BASE.metadata,
    Column('id', Integer, autoincrement=True, primary_key=True),
    Column('book_id', Integer, ForeignKey('book.id'), primary_key=True),
    Column('tag_id', Integer, ForeignKey('tag.id'), primary_key=True),
    Column('create_time', DateTime, default=datetime.now, onupdate=datetime.now)
)


class BookOrm(BASE):
    __tablename__ = 'book'
    id = Column(Integer, autoincrement=True, primary_key=True)
    title = Column(String(100), comment='书名', doc={'zh': '书名'})
    tags = relationship('TagOrm', backref=backref('books', uselist=True), secondary=book_tag_table)

    def __str__(self):
        return f'<BookOrm title={self.title}>'

    __repr__ = __str__


class TagOrm(BASE):
    __tablename__ = 'tag'
    id = Column(Integer, autoincrement=True, primary_key=True)
    title = Column(String(100), comment='标签', doc={'zh': '标签'})

    def __str__(self):
        return f'<TagOrm title={self.title}>'

    __repr__ = __str__


BASE.metadata.create_all()


def add_data():
    tag1 = TagOrm(title='IT')
    tag2 = TagOrm(title='编程开发')
    tag3 = TagOrm(title='Python')
    tag4 = TagOrm(title='JavaScript')
    book1 = BookOrm(title='Python权威指南')
    book2 = BookOrm(title='JavaScript权威指南')
    book1.tags.append(tag1)
    book1.tags.append(tag2)
    book1.tags.append(tag3)
    print(type(book1.tags))
    book2.tags.append(tag1)
    book2.tags.append(tag2)
    book2.tags.append(tag4)
    print(type(book2.tags))
    SESSION.add_all([book1, book2])
    SESSION.commit()


def search_tags_book():
    tags = SESSION.query(TagOrm).all()
    for tag in tags:
        print(tag.title)
        print(tag.books)


def search_book_tags():
    books = SESSION.query(BookOrm).all()
    for book in books:
        print(book.title)
        print(book.tags)


if __name__ == '__main__':
    search_book_tags()
